﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Globalization;

[assembly: CLSCompliant(true)]
namespace University.Workshop.DataAccess
{
    public class DBConnection:IDisposable
    {
        private SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=DBDentalClinic;Integrated Security=True;Pooling=False");
        private bool disposed;

        public void Dispose() 
        {
            this.Dispose(true);            
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    this.connection.Dispose();
                }
            }
            this.disposed = true;
        }

        public bool ConsultationSelect(string value)
        {
            bool successful = false;
            DataTable dataTable = ConsultationSelectDataTable(value);
            if (dataTable.Rows.Count >= 1)
            {
                successful = true;
            }
            else
            {
                successful = false;
            }
            return successful;
            
        }

        public DataTable ConsultationSelectDataTable(string value)
        {
            DataSet dataSet = ExtractData(value);            
            return dataSet.Tables[0];
        }

        private SqlCommand ExecuteSqlCommand(string value) 
        {
            SqlCommand command;
            try
            {
                connection.Open();
                command = new SqlCommand(value,connection);
            }
            catch (SqlException error)
            {
                
                throw new IOException(error.Message);
            }
            return command;
        }

        public DataSet ExtractData(string value)
        {
            using (DataSet dataSet = new DataSet())
            {
                dataSet.Locale = CultureInfo.InvariantCulture;
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(ExecuteSqlCommand(value)))
                {
                    dataAdapter.Fill(dataSet);
                }
                connection.Close();
                return dataSet;
            }
        }
        public bool CommandExecuteConsult(string value)
        {
            bool successful = true;
            SqlCommand command = ExecuteSqlCommand(value);
            int valid=command.ExecuteNonQuery();
            if (valid <= 0) 
            {
                successful = false;
            }
            connection.Close();
            return successful;
        }

    }
}
